﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Data.Schema.ScriptDom.Sql;
using System.Text.RegularExpressions;
using System.Collections.ObjectModel;
using System.Globalization;

namespace SQLite2CELib
{
    public class Errors
    {
        private List<string> _errorMessages;

        public IEnumerable<string> ErrorMessages
        {
            get { return _errorMessages; }
        }

        public Errors()
        {
            _errorMessages = new List<string>();
        }

        public void Add(string errorMessage)
        {
            _errorMessages.Add(errorMessage);
        }

        public void AddRange(IEnumerable<string> errorMessages)
        {
            _errorMessages.AddRange(errorMessages);
        }
    }

    public class GeneratedLinqInfo
    {
        private StringBuilder _linq;
        private Dictionary<String, PropertyDetails> _propertyNameAndTypeInfo;

        public GeneratedLinqInfo(StringBuilder linq, Dictionary<String, PropertyDetails> propertyNameAndTypeInfo)
        {
            _linq = linq;
            _propertyNameAndTypeInfo = propertyNameAndTypeInfo;
        }

        public StringBuilder Linq { get { return _linq; } }
        public Dictionary<String, PropertyDetails> PropertyNameAndTypeInfo { get { return _propertyNameAndTypeInfo; } }
    }

    public static class OperatorMappings
    {
        public static String GetMapping(BinaryExpressionType expType)
        {
            switch (expType)
            {
                case BinaryExpressionType.Add:
                    return "+";
                case BinaryExpressionType.And:
                    return "&&";
                case BinaryExpressionType.BitwiseAnd:
                case BinaryExpressionType.BitwiseOr:
                case BinaryExpressionType.BitwiseXor:
                    return "NOT SUPPORTED";
                case BinaryExpressionType.Divide:
                    return "/";
                case BinaryExpressionType.Equals:
                    return "==";
                case BinaryExpressionType.GreaterThan:
                    return ">";
                case BinaryExpressionType.GreaterThanOrEqualTo:
                    return ">=";
                case BinaryExpressionType.LeftOuterJoin:
                    return "NOT SUPPORTED";
                case BinaryExpressionType.LessThan:
                    return "<";
                case BinaryExpressionType.LessThanOrEqualTo:
                    return "<=";
                case BinaryExpressionType.Modulo:
                    return "%";
                case BinaryExpressionType.Multiply:
                    return "*";
                case BinaryExpressionType.NotEqualToBrackets:
                    return "!=";
                case BinaryExpressionType.NotEqualToExclamation:
                    return "NOT SUPPORTED";
                case BinaryExpressionType.NotGreaterThan:
                    return "NOT SUPPORTED";
                case BinaryExpressionType.NotLessThan:
                    return "NOT SUPPORTED";
                case BinaryExpressionType.Or:
                    return "||";
                case BinaryExpressionType.RightOuterJoin:
                    return "NOT SUPPORTED";
                case BinaryExpressionType.Subtract:
                    return "-";
                default:
                    break;
            }
            return "NOT SUPPORTED";
        }

        public static String GetMapping(SortOrder sortOrderOperator)
        {
            switch (sortOrderOperator)
            {
                case SortOrder.Ascending:
                    return "ascending";
                case SortOrder.Descending:
                    return "descending";
                default:
                case SortOrder.NotSpecified:
                    return "ascending";
            }
        }

        public static String GetAggregateFunctionMapping(String functionName)
        {
            switch (functionName.Trim().ToUpperInvariant())
            {
                case "SUM":
                    return "Sum";
                case "MIN":
                    return "Min";
                case "MAX":
                    return "Max";
                case "AVG":
                    return "Average";
                default:
                    break;
            }
            return String.Empty;
        }

        public static String GetCompoundExpressionMapping(BinaryQueryExpressionType expType, bool all = false)
        {
            switch (expType)
            {
                case BinaryQueryExpressionType.Except:
                    return "Except";
                case BinaryQueryExpressionType.Intersect:
                    return "Intersect";
                case BinaryQueryExpressionType.Union:
                    return all == true ? "Concat" : "Union";
                default:
                    return String.Empty;
            }
        }
    }

    public class BuildLinqForSelectStatement
    {
        private string _linqQueryOrderClause = "orderby {0} {1}";
        private string _linqQueryOrderByAscLambda = ".OrderBy(p => p.{0})";
        private string _linqQueryOrderByDescLambda = ".OrderByDescending(p => p.{0})";

        private Dictionary<String, String> _entityNameAndLeftJoinedEntityName;
        private Dictionary<String, PropertyDetails> _fieldNameAndTypeInfo;

        private StringBuilder _orderByClause;
        private StringBuilder _whereClause;
        private StringBuilder _fromClause;
        private StringBuilder _groupByClause;
        private StringBuilder _selectClause;
        private StringBuilder _uniqueRowFilterClause;

        private List<String> _tablesInQuery;
        private TableSchemaHelper _schemaHelper;
        private Utils _utils;
        private Errors _errors;

        public BuildLinqForSelectStatement()
        {
            _entityNameAndLeftJoinedEntityName = new Dictionary<string, string>();
            _fieldNameAndTypeInfo = new Dictionary<String, PropertyDetails>();
            _tablesInQuery = new List<string>();
            _errors = new Errors();
        }

        public GeneratedLinqInfo GenerateLinq(SelectStatement selectStatementDom,
                                             TableSchemaHelper tableSchemaHelper,
                                             out Errors errors)
        {
            QuerySpecification querySpecs;
            StringBuilder linqQuery = null;
            Dictionary<String, PropertyDetails> firstQueryPropNameAndDetailsMappings;

            errors = _errors;
            try
            {
                _schemaHelper = tableSchemaHelper;
                _utils = new Utils(_schemaHelper, _tablesInQuery, _entityNameAndLeftJoinedEntityName);

                if (selectStatementDom.QueryExpression is QuerySpecification)
                {
                    querySpecs = selectStatementDom.QueryExpression as QuerySpecification;
                    linqQuery = ProcessQuerySpecs(querySpecs, selectStatementDom.OrderByClause);
                }
                if (selectStatementDom.QueryExpression is BinaryQueryExpression)
                {
                    firstQueryPropNameAndDetailsMappings = new Dictionary<string, PropertyDetails>();
                    linqQuery = ProcessBinaryQuerySpecs(selectStatementDom.QueryExpression as BinaryQueryExpression, ref firstQueryPropNameAndDetailsMappings);
                    _fieldNameAndTypeInfo = firstQueryPropNameAndDetailsMappings;
                    ProcessOrderByClauseForBinarySelect(selectStatementDom.OrderByClause, ref linqQuery);
                }
                return new GeneratedLinqInfo(linqQuery, _fieldNameAndTypeInfo);
            }
            catch (NotSupportedException ex)
            {
                _errors.Add(ex.Message);
                return null;
            }
            catch (Exception ex)
            {
                _errors.Add("Error occured while converting the input SQL Query.");
                return null;
            }
        }

        private StringBuilder ProcessUniqueRowFilter(UniqueRowFilter uniqueRowFilter)
        {
            if (uniqueRowFilter == UniqueRowFilter.Distinct)
            {
                return new StringBuilder(".Distinct()");
            }
            return new StringBuilder();
        }

        private void ProcessOrderByClauseForBinarySelect(OrderByClause orderByClause, ref StringBuilder linqQuery)
        {
            if (orderByClause == null || orderByClause.OrderByElements == null || orderByClause.OrderByElements.Count == 0)
            {
                return;
            }
            Column columnInOrderBy;

            //Step 1 - wrap the generate linqQuery inside brackets.
            WrapQueryInsideBracket(ref linqQuery);

            //Append order by lamba clauses one by one for each order by element inside the query.
            foreach (ExpressionWithSortOrder eachOrderByElement in orderByClause.OrderByElements)
            {
                columnInOrderBy = eachOrderByElement.Expression as Column;
                if (columnInOrderBy == null)
                {
                    throw new NotSupportedException("Order by clause has some unsupported tsql syntax.");
                }

                if (eachOrderByElement.SortOrder == SortOrder.Descending)
                {
                    linqQuery.AppendFormat(_linqQueryOrderByDescLambda, columnInOrderBy.Identifiers[0].Value);
                }
                else
                {
                    linqQuery.AppendFormat(_linqQueryOrderByAscLambda, columnInOrderBy.Identifiers[0].Value);
                }
            }
        }

        private StringBuilder ProcessBinaryQuerySpecs(BinaryQueryExpression binaryQueryExpression,
                                                      ref Dictionary<String, PropertyDetails> firstQueryPropNameAndDetailsMappings)
        {
            StringBuilder linqQuery;
            StringBuilder linqQueryForFirstExp = null, linqQueryForSecondExp = null;

            linqQuery = new StringBuilder();

            if (binaryQueryExpression.FirstQueryExpression is QuerySpecification)
            {
                linqQueryForFirstExp = ProcessQuerySpecs(binaryQueryExpression.FirstQueryExpression as QuerySpecification);
                //Put the property name and details mappings in the dictionary meant for 
                if (firstQueryPropNameAndDetailsMappings.Count == 0)
                {
                    foreach (KeyValuePair<String, PropertyDetails> item in _fieldNameAndTypeInfo)
                    {
                        firstQueryPropNameAndDetailsMappings.Add(item.Key, item.Value);
                    }
                    //Clear off the existing values in the _fieldNameAndTypeInfo so that if next query contains
                    //some literal column then we can add it otherwise there would be naming conflict...
                    _fieldNameAndTypeInfo.Clear();
                }
                else
                {
                    linqQueryForFirstExp = ChangePropertyNameForAnonymousTypeInSelect(linqQueryForFirstExp, _fieldNameAndTypeInfo, firstQueryPropNameAndDetailsMappings);
                }
            }
            else if (binaryQueryExpression.FirstQueryExpression is BinaryQueryExpression)
            {
                linqQueryForFirstExp = ProcessBinaryQuerySpecs(binaryQueryExpression.FirstQueryExpression as BinaryQueryExpression, ref firstQueryPropNameAndDetailsMappings);
            }

            //Clean up table name .. list so that it does not interfear in second query processing.
            _tablesInQuery.Clear();

            if (binaryQueryExpression.SecondQueryExpression is QuerySpecification)
            {
                linqQueryForSecondExp = ProcessQuerySpecs(binaryQueryExpression.SecondQueryExpression as QuerySpecification);
                //Put the property name and details mappings in the dictionary meant for 
                if (firstQueryPropNameAndDetailsMappings.Count == 0)
                {
                    foreach (KeyValuePair<String, PropertyDetails> item in _fieldNameAndTypeInfo)
                    {
                        firstQueryPropNameAndDetailsMappings.Add(item.Key, item.Value);
                    }
                    _fieldNameAndTypeInfo.Clear();
                }
                else
                {
                    linqQueryForSecondExp = ChangePropertyNameForAnonymousTypeInSelect(linqQueryForSecondExp, _fieldNameAndTypeInfo, firstQueryPropNameAndDetailsMappings);
                }
            }
            else if (binaryQueryExpression.SecondQueryExpression is BinaryQueryExpression)
            {
                linqQueryForSecondExp = ProcessBinaryQuerySpecs(binaryQueryExpression.SecondQueryExpression as BinaryQueryExpression, ref firstQueryPropNameAndDetailsMappings);
            }

            //Wrap query for first exp and second exp inside bracket and insert appropriate operator in between them.
            WrapQueryInsideBracket(ref linqQueryForFirstExp);
            WrapQueryInsideBracket(ref linqQueryForSecondExp);

            linqQuery = new StringBuilder();
            linqQuery.Append(linqQueryForFirstExp);
            linqQuery.Append(".");
            linqQuery.Append(OperatorMappings.GetCompoundExpressionMapping(binaryQueryExpression.BinaryQueryExpressionType, binaryQueryExpression.All));
            linqQuery.Append(Environment.NewLine);
            linqQuery.Append(linqQueryForSecondExp);

            return linqQuery;
        }

        private StringBuilder ProcessQuerySpecs(QuerySpecification querySpecs, OrderByClause orderByClause = null)
        {
            WhereClauseConverter whereClauseConverter;
            SelectClauseConverter selectClauseConverter;
            FromClauseConverter fromClauseConverter;
            GroupByConverter groupByConverter;
            Dictionary<String, List<PropertyDetails>> groupingFieldSpecs = null;

            fromClauseConverter = new FromClauseConverter(_schemaHelper, _utils);
            _utils.TableAliasAndTableName = fromClauseConverter.TableNameAndAliasInfo;
            _fromClause = fromClauseConverter.Convert(querySpecs.FromClauses, ref _tablesInQuery, ref _entityNameAndLeftJoinedEntityName);

            if (querySpecs.WhereClause != null)
            {
                whereClauseConverter = new WhereClauseConverter(_schemaHelper, _utils, _errors);
                _whereClause = whereClauseConverter.Convert(querySpecs.WhereClause);
                _whereClause.Append(Environment.NewLine);
            }

            if (querySpecs.GroupByClause != null)
            {
                groupByConverter = new GroupByConverter(_utils);
                _groupByClause = groupByConverter.Convert(querySpecs.GroupByClause, ref _tablesInQuery, out groupingFieldSpecs);
            }

            _orderByClause = new StringBuilder();
            if (orderByClause != null)
            {
                ExtractOrderByClause(orderByClause, _utils, ref _orderByClause);
            }

            if (querySpecs.SelectElements != null)
            {
                selectClauseConverter = new SelectClauseConverter(_schemaHelper, _utils);
                _selectClause = selectClauseConverter.Convert(querySpecs.SelectElements,
                                                              _groupByClause != null,
                                                              groupingFieldSpecs,
                                                              _tablesInQuery,
                                                              ref _fieldNameAndTypeInfo);
            }

            if (querySpecs.UniqueRowFilter != UniqueRowFilter.NotSpecified)
            {
                _uniqueRowFilterClause = ProcessUniqueRowFilter(querySpecs.UniqueRowFilter);
            }
            return GenerateLinqQuery();
        }

        private StringBuilder ChangePropertyNameForAnonymousTypeInSelect(StringBuilder inputLinqQuery,
            Dictionary<string, PropertyDetails> currentPropertyNames,
            Dictionary<string, PropertyDetails> newNamesToBeReplaced)
        {
            int index;
            String linqQueryString;
            String regexForCurrentProperty;
            String regexToFindPropInSelectClause;

            regexToFindPropInSelectClause = @"(\sselect*\snew*\s{{\s*.*)({0})(\s=)"; //0= property name.

            //Assumption -- both actual property name and property name to be replaced have same number of items
            //           -- 1 st property name in actual property name is replace with 1st item in property name to be replaced.
            linqQueryString = inputLinqQuery.ToString();

            index = 0;
            foreach (KeyValuePair<String, PropertyDetails> eachCurrentProperty in currentPropertyNames)
            {
                regexForCurrentProperty = String.Format(CultureInfo.InvariantCulture, regexToFindPropInSelectClause, eachCurrentProperty.Key);

                KeyValuePair<String, PropertyDetails> newPropertyName = newNamesToBeReplaced.ElementAt(index++);
                linqQueryString = Regex.Replace(linqQueryString, regexForCurrentProperty, String.Concat("$1 ", newPropertyName.Key, " $3"));
            }
            return new StringBuilder(linqQueryString);
        }

        private void ExtractOrderByClause(OrderByClause orderByClause, Utils utils, ref StringBuilder linqOrderByClause)
        {
            String columnNameInSortExp;
            String sortOperator;
            IList<ExpressionWithSortOrder> orderByElements;
            KeyValuePair<String, PropertyDetails> matchingClassNameAndTypeInfo;

            orderByElements = orderByClause.OrderByElements;

            foreach (ExpressionWithSortOrder expWithSortOrder in orderByElements)
            {
                columnNameInSortExp = utils.ExtractValueFromExpression(expWithSortOrder.Expression, out matchingClassNameAndTypeInfo).ToString();
                sortOperator = OperatorMappings.GetMapping(expWithSortOrder.SortOrder);
                linqOrderByClause.AppendFormat(_linqQueryOrderClause, columnNameInSortExp, sortOperator);
                linqOrderByClause.Append(Environment.NewLine);
            }
        }

        private StringBuilder GenerateLinqQuery()
        {
            StringBuilder linqQuery;

            linqQuery = new StringBuilder();
            linqQuery.Append(_fromClause != null ? _fromClause.ToString() : String.Empty);
            linqQuery.Append(_whereClause != null ? _whereClause.ToString() : String.Empty);
            linqQuery.Append(_groupByClause != null ? _groupByClause.ToString() : String.Empty);
            linqQuery.Append(_orderByClause != null ? _orderByClause.ToString() : String.Empty);
            linqQuery.Append(_selectClause != null ? _selectClause.ToString() : String.Empty);

            if (_uniqueRowFilterClause != null)
            {
                //Wrap entire query inside brackets and append filter clause.
                linqQuery.Insert(0, "(");
                linqQuery.Insert(linqQuery.Length, ")");
                linqQuery.Append(_uniqueRowFilterClause);
            }

            return linqQuery;
        }

        private void WrapQueryInsideBracket(ref StringBuilder query)
        {
            query.Insert(0, "(");
            query.Insert(query.Length, ")");
        }
    }
}
